package dao.impl;

import com.mysql.cj.util.StringUtils;
import com.sun.javafx.iio.common.SmoothMinifier;
import dao.SmbmsUserDao;
import pojo.SmbmsUser;
import service.SmbmsUserService;
import utils.BaseDao;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SmbmsUserDaoImpl extends BaseDao implements SmbmsUserDao {
    //通过用户编码查询用户信息
    @Override
    public SmbmsUser queryUserByUserCode(String userCode) throws Exception {
        SmbmsUser smbmsUser = new SmbmsUser();
        String sql = "select * from smbms_user where userCode=?";
        Object[] params = {userCode};
        ResultSet resultSet = executeSQL(sql, params);
        while (resultSet.next()) {
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setUserPassword(resultSet.getString("userPassword"));
        }
        return smbmsUser;
    }
    //查询用户信息并支持模糊查询支持角色查询并分页
    @Override
    public List<SmbmsUser> queryUserByUserName(String username,int userRole,int currentPageNo,int pageSize) throws Exception {
        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if(!StringUtils.isNullOrEmpty(username)){
            sql.append(" and u.userName like ?");
            obj.add("%"+username+"%");
        }
        if(userRole>0){
            sql.append(" and u.userRole=?");
            obj.add(userRole);
        }
//分页
        sql.append(" order by u.creationDate desc limit ?,?");
        obj.add((currentPageNo-1)*pageSize);
        obj.add(pageSize);
        Object[] params=obj.toArray();
        System.out.println(sql.toString()+"========================");
        System.out.println(params+"=====================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next()){
            SmbmsUser smbmsUser=new SmbmsUser();
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
//把对象放在集合中
            list.add(smbmsUser);
        }
        return list;
    }

    //总条数
    @Override
    public int getSmbmsUserCount(String username,int userRole) throws Exception {
        int count=0;
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select count(*) from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if(!StringUtils.isNullOrEmpty(username)){
            sql.append(" and u.userName like ?");
            obj.add("%"+username+"%");
        }
        if(userRole>0){
            sql.append(" and u.userRole=?");
            obj.add(userRole);
        }
        Object[] params=obj.toArray();
        System.out.println(sql+"==========================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next()){
            count=resultSet.getInt(1);
        }
        return count;
    }

    //通过id查询用户信息
    @Override
    public SmbmsUser queryUserById(Long id) throws Exception {
        SmbmsUser smbmsUser=new SmbmsUser();
        String sql="select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id and u.id=?";
        Object[] params={id};
        ResultSet resultSet = executeSQL(sql, params);
        while(resultSet.next()){
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setAddress(resultSet.getString("address"));
            smbmsUser.setUserRole(resultSet.getInt("userRole"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
        }
        return smbmsUser;
    }

    //修改操作
    @Override
    public int updateUser(SmbmsUser smbmsUser) throws Exception {
        int count=0;
        String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=?";
        Object[] params=
                {smbmsUser.getUserName(),smbmsUser.getGender(),smbmsUser.getBirthday(),smbmsUser.
                        getPhone(),smbmsUser.getAddress(),smbmsUser.getUserRole(),smbmsUser.getId()};
        count= executeUpdate(sql, params);
        return count;
    }

    //增加功能
    @Override
    public int addUser(SmbmsUser smbmsUser) throws Exception {
        int count=0;
        String sql="insert into smbms_user(userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, " +
                "creationDate)values(?,?,?,?,?,?,?,?,?,?)";
        Object[] params= {smbmsUser.getUserCode(),smbmsUser.getUserName(),smbmsUser.getUserPassword(),smbmsUser.getGender(),
                smbmsUser.getBirthday(),smbmsUser.getPhone(),smbmsUser.getAddress(),smbmsUser.getUserRole(),smbmsUser.getCreatedBy(),
                smbmsUser.getCreationDate()};
        count = executeUpdate(sql, params);
        return count;
    }

    //删除功能
    @Override
    public int deleteUser(Long id) throws Exception {
        int count=0;
        String sql="delete from smbms_user where id=?";
        Object[] params={id};
        count = executeUpdate(sql, params);
        return count;
    }

    @Override
    public int updatePassword(SmbmsUser smbmsUser) throws Exception {
        int count = 0;
        String sql = "update smbms_user set userPassword=? where id = 1 ";
        Object[] params = {smbmsUser.getUserPassword()};
        count=executeUpdate(sql,params);
        return count;
    }


}

//2021.01.12
//    @Override
//    public List<SmbmsUser> queryUserByUserName(String username, int userRole) throws Exception {
//        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
//        StringBuffer sql=new StringBuffer();
//        List<Object> obj=new ArrayList<Object>();
//        sql.append("select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id");
//        if(!StringUtils.isNullOrEmpty(username)){
//            sql.append(" and u.userName like ?");
//            obj.add("%"+username+"%");
//        }
//        if(userRole>0){
//            sql.append(" and u.userRole=?");
//            obj.add(userRole);
//        }
//        Object[] params=obj.toArray();
//        System.out.println(sql.toString()+"========================");
//        System.out.println(params+"=====================");
//        ResultSet resultSet = executeSQL(sql.toString(), params);
//        while(resultSet.next()){
//            SmbmsUser smbmsUser=new SmbmsUser();
//            smbmsUser.setId(resultSet.getLong("id"));
//            smbmsUser.setUserCode(resultSet.getString("userCode"));
//            smbmsUser.setUserName(resultSet.getString("userName"));
//            smbmsUser.setGender(resultSet.getInt("gender"));
//            smbmsUser.setBirthday(resultSet.getDate("birthday"));
//            smbmsUser.setPhone(resultSet.getString("phone"));
//            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
////把对象放在集合中
//            list.add(smbmsUser);
//        }
//        return list;
//    }
//
//    //分页
//    @Override
//    public List<SmbmsUser> queryList(int currentPageNo, int pageSize) throws
//            Exception {
//        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
//        String sql="select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id order by u.creationDate desc limit ?,?";
//        Object[] params={(currentPageNo-1)*pageSize,pageSize};
//        ResultSet resultSet = executeSQL(sql, params);
//        while(resultSet.next()){
//            SmbmsUser smbmsUser=new SmbmsUser();
//            smbmsUser.setId(resultSet.getLong("id"));
//            smbmsUser.setUserCode(resultSet.getString("userCode"));
//            smbmsUser.setUserName(resultSet.getString("userName"));
//            smbmsUser.setGender(resultSet.getInt("gender"));
//            smbmsUser.setBirthday(resultSet.getDate("birthday"));
//            smbmsUser.setPhone(resultSet.getString("phone"));
//            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
////把对象放在集合中
//            list.add(smbmsUser);
//        }
//        return list;
//    }
//    //总条数
//    @Override
//    public int getSmbmsUserCount() throws Exception {
//        int count=0;
//        String sql="select count(*) from smbms_user u inner join smbms_role r on u.userRole=r.id order by u.creationDate desc";
//        Object[] params={};
//        ResultSet resultSet = executeSQL(sql, params);
//        while(resultSet.next()){
//            count=resultSet.getInt(1);
//        }
//        return count;
//    }
//
//}
//2021.01.11
//    @Override
//    public List<SmbmsUser> queryList() throws Exception {
//        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
//        String sql="select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id";
//        Object[] params={};
//        ResultSet resultSet = executeSQL(sql, params);
//        while(resultSet.next()){
//            SmbmsUser smbmsUser=new SmbmsUser();
//            smbmsUser.setId(resultSet.getLong("id"));
//            smbmsUser.setUserCode(resultSet.getString("userCode"));
//            smbmsUser.setUserName(resultSet.getString("userName"));
//            smbmsUser.setGender(resultSet.getInt("gender"));
//            smbmsUser.setBirthday(resultSet.getDate("birthday"));
//            smbmsUser.setPhone(resultSet.getString("phone"));
//            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
////把对象放在集合中
//            list.add(smbmsUser);
//        }
//        return list;
//    }

//    @Override
//    public List<SmbmsUser> queryUserByUserName(String username) throws Exception
//    {
//        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
//        String sql="select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id where u.userName like ?";
//        Object[] params={'%'+username+'%'};
//        ResultSet resultSet = executeSQL(sql, params);
//        while(resultSet.next()){
//            SmbmsUser smbmsUser=new SmbmsUser();
//            smbmsUser.setId(resultSet.getLong("id"));
//            smbmsUser.setUserCode(resultSet.getString("userCode"));
//            smbmsUser.setUserName(resultSet.getString("userName"));
//            smbmsUser.setGender(resultSet.getInt("gender"));
//            smbmsUser.setBirthday(resultSet.getDate("birthday"));
//            smbmsUser.setPhone(resultSet.getString("phone"));
//            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
////把对象放在集合中
//            list.add(smbmsUser);
//        }
//        return list;
//    }

    //查询用户信息并支持模糊查询
//    @Override
//    public List<SmbmsUser> queryUserByUserName(String username) throws Exception
//    {
//        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
//        StringBuffer sql=new StringBuffer();
//        List<Object> obj=new ArrayList<Object>();
//        sql.append("select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id");
//        if(!StringUtils.isNullOrEmpty(username)){
//            sql.append(" where u.userName like ?");
//            obj.add("%"+username+"%");
//        }
//        Object[] params=obj.toArray();
//        System.out.println(sql.toString()+"========================");
//        System.out.println(params+"=====================");
//        ResultSet resultSet = executeSQL(sql.toString(), params);
//        while(resultSet.next()){
//            SmbmsUser smbmsUser=new SmbmsUser();
//            smbmsUser.setId(resultSet.getLong("id"));
//            smbmsUser.setUserCode(resultSet.getString("userCode"));
//            smbmsUser.setUserName(resultSet.getString("userName"));
//            smbmsUser.setGender(resultSet.getInt("gender"));
//            smbmsUser.setBirthday(resultSet.getDate("birthday"));
//            smbmsUser.setPhone(resultSet.getString("phone"));
//            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
////把对象放在集合中
//            list.add(smbmsUser);
//        }
//        return list;
//    }


